﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Matrix.Common.DBUtility;
using Matrix.MaKaYunShang.Model;
using Matrix.MaKaYunShang.Model.Dto;

namespace Matrix.MaKaYunShang.DAL
{
    public partial class ShopCommodityDAL : BaseDAL<ShopCommodityDAL>
    {
        #region 默认方法
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(ShopCommodity model)
        {
            var strSql = new StringBuilder();
            strSql.Append("insert into ShopCommodity(");
            strSql.Append("CommodityLibId,ShopId,SoldPrice,Quantity,SalesVolume,DisplaySort,SellingStatus,ShopCommission,AddTime,IsDelete");
            strSql.Append(") values (");
            strSql.Append("@CommodityLibId,@ShopId,@SoldPrice,@Quantity,@SalesVolume,@DisplaySort,@SellingStatus,@ShopCommission,@AddTime,@IsDelete");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
			            new SqlParameter("@CommodityLibId", SqlDbType.Int,4) ,            
                        new SqlParameter("@ShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@SoldPrice", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@Quantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@SalesVolume", SqlDbType.Int,4) ,            
                        new SqlParameter("@DisplaySort", SqlDbType.Int,4) ,            
                        new SqlParameter("@SellingStatus", SqlDbType.Bit,1) ,            
                        new SqlParameter("@ShopCommission", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@AddTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.CommodityLibId;
            parameters[1].Value = model.ShopId;
            parameters[2].Value = model.SoldPrice;
            parameters[3].Value = model.Quantity;
            parameters[4].Value = model.SalesVolume;
            parameters[5].Value = model.DisplaySort;
            parameters[6].Value = model.SellingStatus;
            parameters[7].Value = model.ShopCommission;
            parameters[8].Value = model.AddTime;
            parameters[9].Value = model.IsDelete; var obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(ShopCommodity model)
        {
            var strSql = new StringBuilder();
            strSql.Append("update ShopCommodity set ");

            strSql.Append(" CommodityLibId = @CommodityLibId , ");
            strSql.Append(" ShopId = @ShopId , ");
            strSql.Append(" SoldPrice = @SoldPrice , ");
            strSql.Append(" Quantity = @Quantity , ");
            strSql.Append(" SalesVolume = @SalesVolume , ");
            strSql.Append(" DisplaySort = @DisplaySort , ");
            strSql.Append(" SellingStatus = @SellingStatus , ");
            strSql.Append(" ShopCommission = @ShopCommission , ");
            strSql.Append(" AddTime = @AddTime , ");
            strSql.Append(" IsDelete = @IsDelete  ");
            strSql.Append(" where Id=@Id ");

            SqlParameter[] parameters = {
			            new SqlParameter("@Id", SqlDbType.Int,4) ,            
                        new SqlParameter("@CommodityLibId", SqlDbType.Int,4) ,            
                        new SqlParameter("@ShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@SoldPrice", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@Quantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@SalesVolume", SqlDbType.Int,4) ,            
                        new SqlParameter("@DisplaySort", SqlDbType.Int,4) ,            
                        new SqlParameter("@SellingStatus", SqlDbType.Bit,1) ,            
                        new SqlParameter("@ShopCommission", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@AddTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.Id;
            parameters[1].Value = model.CommodityLibId;
            parameters[2].Value = model.ShopId;
            parameters[3].Value = model.SoldPrice;
            parameters[4].Value = model.Quantity;
            parameters[5].Value = model.SalesVolume;
            parameters[6].Value = model.DisplaySort;
            parameters[7].Value = model.SellingStatus;
            parameters[8].Value = model.ShopCommission;
            parameters[9].Value = model.AddTime;
            parameters[10].Value = model.IsDelete; return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("update ShopCommodity set IsDelete=1 ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public bool DeleteList(string Idlist)
        {
            var strSql = new StringBuilder();
            strSql.Append("update ShopCommodity set IsDelete=1 ");
            strSql.Append(" where Id in (" + Idlist + ")  ");
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataSet GetModel(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("select Id, CommodityLibId, ShopId, SoldPrice, Quantity, SalesVolume, DisplaySort, SellingStatus, ShopCommission, AddTime, IsDelete  ");
            strSql.Append("  from ShopCommodity ");
            strSql.Append(" where Id=@Id  and IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            var ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM ShopCommodity where IsDelete=0 ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion

        #region 扩展方法

        /// <summary>
        /// 获取分页数据
        /// </summary>
        public List<ViewShopCommodityDto> GetPagingList(int pageIndex, int pageSize, string searchKeyWord, string colOrder, int shopId, ref int totalCount)
        {
            string strWhere = " IsDelete = 0 ";
            if (shopId > 0)
            {
                strWhere += " and ShopId =" + shopId;
            }
            if (!string.IsNullOrEmpty(searchKeyWord))
            {
                strWhere += " AND (CommodityName LIKE '%" + searchKeyWord + "%' OR SoldPrice LIKE '%" + searchKeyWord +
                            "%' OR Quantity LIKE '%" + searchKeyWord + "%') ";
            }
            const string tables = "(SELECT sc.Id, cl.CommodityName, cl.HeadPic, s.Id AS ShopId, s.Name AS ShopName, sc.SoldPrice, sc.Quantity, sc.SellingStatus, sc.ShopCommission, sc.IsDelete, cl.IsLanmaSold, sc.DisplaySort " +
                                  "FROM  ShopCommodity sc " +
                                  "INNER JOIN CommodityLib cl ON sc.CommodityLibId = cl.Id " +
                                  "INNER JOIN Shops s ON sc.ShopId = s.Id " +
                                  "WHERE sc.IsDelete = 0 AND cl.IsDelete = 0 AND s.IsDelete = 0 ) c ";

            if (string.IsNullOrWhiteSpace(colOrder))
            {
                colOrder = " IsLanmaSold DESC, DisplaySort DESC ";
            }
            return GetSimpleListPage<ViewShopCommodityDto>(pageIndex, pageSize, tables, null, null, colOrder, strWhere, ref totalCount);
        }

        /// <summary>
        /// 获取店铺商品售卖列表
        /// </summary>
        /// <param name="sellingStatus">NULL 选取所有 0 下架 1 上架</param>
        public List<ShopCommodity> GetShopCommodityList(int shopId, int cateId, int? sellingStatus)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("SELECT sc.Id, sc.CommodityLibId, sc.ShopId, sc.SoldPrice, sc.Quantity, sc.SalesVolume, sc.DisplaySort, ");
            sbSql.Append("sc.SellingStatus, sc.ShopCommission, sc.AddTime, sc.IsDelete, ss.[Name] AS ShopName  ");
            sbSql.Append(" FROM ShopCommodity sc ");
            sbSql.Append(" INNER JOIN Shops ss ON ss.Id=sc.ShopId ");
            sbSql.Append(" INNER JOIN CommodityLib cl ON cl.Id=sc.CommodityLibId ");
            sbSql.Append(" WHERE sc.ShopId=@ShopId AND cl.CateId=@CateId AND sc.IsDelete=0 AND ss.IsDelete=0 and cl.IsDelete=0");
            if (sellingStatus != null)
            {
                sbSql.Append("AND sc.SellingStatus=" + (int)sellingStatus);
            }
            sbSql.Append(" ORDER BY cl.IsLanmaSold DESC, sc.DisplaySort DESC ");

            SqlParameter[] parameters = {
					new SqlParameter("@ShopId", SqlDbType.Int,4),
                    new SqlParameter("@CateId", SqlDbType.Int,4)
			};

            parameters[0].Value = shopId;
            parameters[1].Value = cateId;

            var lsResult = DbHelperSQL.ExecuteSqlList<ShopCommodity>(sbSql.ToString(), parameters);
            if (lsResult != null && lsResult.Count > 0)
            {
                return lsResult.ToList();
            }
            return null;
        }

        /// <summary>
        /// 根据店铺商品ID集合获取店铺商品列表
        /// </summary>
        public List<ShopCommodity> GetShopCommodityListAppointedIds(int shopId, string shopCommodityIds)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("SELECT sc.Id, sc.CommodityLibId, sc.ShopId, sc.SoldPrice, sc.Quantity, sc.SalesVolume, sc.DisplaySort, ");
            sbSql.Append("sc.SellingStatus, sc.ShopCommission, sc.AddTime, sc.IsDelete, ss.[Name] AS ShopName ");
            sbSql.Append("FROM ShopCommodity sc ");
            sbSql.Append("INNER JOIN Shops ss ON ss.Id=sc.ShopId ");
            sbSql.Append("INNER JOIN CommodityLib cl ON cl.Id=sc.CommodityLibId ");
            sbSql.Append("WHERE sc.ShopId=@ShopId AND sc.IsDelete=0 AND ss.IsDelete=0 ");
            sbSql.Append("AND sc.Id IN (" + shopCommodityIds + ")");
            sbSql.Append("ORDER BY cl.IsLanmaSold DESC, sc.DisplaySort DESC");
            SqlParameter[] parameters = {
					new SqlParameter("@ShopId", SqlDbType.Int,4)
			};
            parameters[0].Value = shopId;
            var lsResult = DbHelperSQL.ExecuteSqlList<ShopCommodity>(sbSql.ToString(), parameters);
            if (lsResult != null && lsResult.Count > 0)
            {
                return lsResult.ToList();
            }
            return null;
        }

        /// <summary>
        /// 通过平台售卖商品ID删除店铺商品关系数据
        /// </summary>
        public bool DeleteByLanmaSoldCommodityIds(string idlist)
        {
            var strSql = new StringBuilder();
            strSql.Append("update sc set sc.IsDelete=1 ");
            strSql.Append("FROM ShopCommodity sc ");
            strSql.Append("INNER JOIN CommodityLib cl ON cl.Id=sc.CommodityLibId ");
            strSql.Append(" where cl.IsLanmaSold=0 AND CommodityLibId in (" + idlist + ") ");
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 获取对象信息含扩展
        /// </summary>
        public ShopCommodity GetModelWithExtendInfo(int id)
        {
            var strSql = new StringBuilder();
            strSql.Append("select cl.LowerPrice,cl.CeilingPrice,sc.Id, sc.CommodityLibId, sc.ShopId, sc.SoldPrice, sc.Quantity, sc.SalesVolume, sc.DisplaySort, sc.SellingStatus, sc.ShopCommission, sc.AddTime, sc.IsDelete, ss.Name AS ShopName, cl.IsLanmaSold, cl.IsShopCanModifyPrice  ");
            strSql.Append("  from ShopCommodity sc ");
            strSql.Append(" INNER JOIN Shops ss ON ss.Id=sc.ShopId ");
            strSql.Append(" LEFT JOIN CommodityLib cl ON cl.Id=sc.CommodityLibId ");
            strSql.Append(" where sc.Id=@Id  and sc.IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;
            return DbHelperSQL.ExecuteReader<ShopCommodity>(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 通过店铺ID以及商品ID得到一个对象实体
        /// </summary>
        public DataSet GetModelByShopIdAndCommodityId(int shopId, int commodityId)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * from ShopCommodity ");
            strSql.Append(" where ShopId=@ShopId AND CommodityLibId=@CommodityLibId ");
            SqlParameter[] parameters = {
					new SqlParameter("@shopId", SqlDbType.Int,4),
                    new SqlParameter("@CommodityLibId", SqlDbType.Int,4)
			};
            parameters[0].Value = shopId;
            parameters[1].Value = commodityId;
            var ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }

        /// <summary>
        /// 更新售卖商品
        /// </summary>
        public bool UpdateShopCommodityByCommodityIds(string ids, decimal soldPrice, decimal shopCommission, int displaySort)
        {
            var strSql = new StringBuilder();
            strSql.Append("update sc set ");
            strSql.Append(" sc.SoldPrice = @SoldPrice, sc.DisplaySort=@DisplaySort,ShopCommission=@ShopCommission ");
            strSql.Append("FROM ShopCommodity sc ");
            strSql.Append("INNER JOIN CommodityLib cl ON cl.Id=sc.CommodityLibId ");
            strSql.Append(" where cl.IsLanmaSold=1 AND CommodityLibId in (" + ids + ") ");
            SqlParameter[] parameters = {
				new SqlParameter("@SoldPrice", SqlDbType.Decimal,9),
				new SqlParameter("@DisplaySort", SqlDbType.Int,4),
                new SqlParameter("@ShopCommission", SqlDbType.Decimal,9)
			};
            parameters[0].Value = soldPrice;
            parameters[1].Value = displaySort;
            parameters[2].Value = shopCommission;
            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetListWithOutIsDelete(string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * FROM ShopCommodity ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 恢复数据（IsDelete字段，同时修改SellingStatus）
        /// </summary>
        public bool UpdateIsDeleteByCommodityLibId(int commodityLibId)
        {
            var strSql = new StringBuilder();
            strSql.Append("update ShopCommodity set ");
            strSql.Append(" IsDelete=0,SellingStatus=1 ");
            strSql.Append(" where CommodityLibId=@CommodityLibId ");
            SqlParameter[] parameters = {            
                new SqlParameter("@CommodityLibId", SqlDbType.Int,4)
            };
            parameters[0].Value = commodityLibId;
            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 增加多条数据
        /// </summary>
        /// <remarks>2016-7-27 万乐园 新增</remarks>
        public int AddList(List<int> shopIdList, ShopCommodity newModel)
        {
            var strSql = new StringBuilder();
            foreach (var item in shopIdList)
            {
                strSql.Append("insert into ShopCommodity(");
                strSql.Append("CommodityLibId,ShopId,SoldPrice,Quantity,SalesVolume,DisplaySort,SellingStatus,AddTime,IsDelete,ShopCommission");
                strSql.Append(") values (");
                strSql.Append(newModel.CommodityLibId + ",");
                strSql.Append(item + ",");
                strSql.Append(newModel.SoldPrice + ",");
                strSql.Append(newModel.Quantity + ",");
                strSql.Append(newModel.SalesVolume + ",");
                strSql.Append(newModel.DisplaySort + ",");
                strSql.Append("1,");//默认上架
                strSql.Append("'" + newModel.AddTime + "',");
                strSql.Append("0,");//默认非删除
                strSql.Append("'" + newModel.ShopCommission + "'");
                strSql.Append(");");
            }
            strSql.Append("select @@IDENTITY");
            var obj = DbHelperSQL.GetSingle(strSql.ToString());
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public ShopCommodity GetModelInfoIncludeDelete(int id)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * from ShopCommodity ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
				new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;
            return DbHelperSQL.ExecuteReader<ShopCommodity>(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 根据商品库ID更改店铺商品排序
        /// </summary>
        /// <remarks>2016-8-19 万乐园 新增</remarks>
        public bool UpdateDisplaySortByCommodityLibId(int displaySort, int commodityLibId)
        {
            return DbHelperSQL.ExecuteSql("update ShopCommodity set [DisplaySort]=" + displaySort + " where [CommodityLibId]=" + commodityLibId) > 0;
        }

        /// <summary>
        /// 根据商品库ID集合以及店铺ID获取店铺商品列表
        /// </summary>
        public List<ShopCommodity> GetListAppointedCommofityLibIds(int shopId, string commodityLibIds)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("SELECT sc.Id, sc.CommodityLibId, sc.ShopId, sc.SoldPrice, sc.Quantity, sc.SalesVolume, sc.DisplaySort, ");
            sbSql.Append("sc.SellingStatus, sc.ShopCommission, sc.AddTime, sc.IsDelete, ss.[Name] AS ShopName ");
            sbSql.Append("FROM ShopCommodity sc ");
            sbSql.Append("INNER JOIN Shops ss ON ss.Id=sc.ShopId ");
            sbSql.Append("INNER JOIN CommodityLib cl ON cl.Id=sc.CommodityLibId ");
            sbSql.Append("WHERE sc.ShopId=@ShopId AND sc.IsDelete=0 AND ss.IsDelete=0 ");
            sbSql.Append("AND sc.CommodityLibId IN (" + commodityLibIds + ")");
            sbSql.Append("ORDER BY cl.IsLanmaSold DESC, sc.DisplaySort DESC");
            SqlParameter[] parameters = {
					new SqlParameter("@ShopId", SqlDbType.Int,4)
			};
            parameters[0].Value = shopId;
            var lsResult = DbHelperSQL.ExecuteSqlList<ShopCommodity>(sbSql.ToString(), parameters);
            if (lsResult != null && lsResult.Count > 0)
            {
                return lsResult.ToList();
            }
            return null;
        }
        #endregion
    }
}
